---
title: "cp_get_agent_online_policy_list stored procedure documentation"
summary: "Analysis of the cp_get_agent_online_policy_list SQL Server (BDS) stored procedure including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "eisD0P11.dbo"
source_name: "cp_get_agent_online_policy_list"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_get_agent_online_policy_list"
retrieval_date: "2026-08-31"
---

# cp_get_agent_online_policy_list

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `eisD0P11.dbo` |
| **Type** | SQL Server stored procedure (BDS) |
| **Database** | SQL Server (`eisD0P11`, referred to internally as "BDS") |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends sqlserver query --name cp_get_agent_online_policy_list` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicySecurity` |

## Purpose

`cp_get_agent_online_policy_list` returns active and recently-closed policies for an agent or agency,
with display flags for rate visibility, sales plan classification, pending-cancel status, current
certificates, and group name, used by `clsPolicySecurity.GetPolicyListForAgent`.

## Procedures / Methods

### cp_get_agent_online_policy_list

| Field | Value |
| --- | --- |
| **Returns** | Policy list rows with `displayType`, `pendingCancel`, `salesPlan`, `groupName`, `viewableRate`, `currentCerts` display flags |
| **Called by** | `clsPolicySecurity.GetPolicyListForAgent` (`clsPolicySecurity.vb:324-336`) |

**Description:** Combines an active-policy branch (`displayType = 'O'`) and a closed-within-five-years
branch (`displayType = 'C'`) via `UNION`, deriving several display flags per row (see rules PR-BVP-056
through PR-BVP-062 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@AgentNum` | IN | `int` | — | Agent or agency number |
| `@ForAgency` | IN | `char(3)` | — | `YES` ⇒ treat `@AgentNum` as an agency number |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-056 | The list combines active policies and policies closed within the last five years | active: `ACTV_PLCY_FL = 'Y'`; closed: `ACTV_PLCY_FL = 'N'` AND `plcy_pd_eff_dt >= DateAdd(YYYY,-5,getDate())` | `UNION` both branches; `displayType` `'O'` for active, `'C'` for closed |
| PR-BVP-057 | `@ForAgency` switches the identifier between agency and agent | `upper(@ForAgency) = 'YES'` → match `wAgt.agncy_num = @AgentNum`; otherwise match `wAgt.agt_num = @AgentNum` | Restrict the result to that book of business |
| PR-BVP-058 | Rate information is viewable only for the policy's own rate account or an in-book rate account | `rat_acct IS NULL` → `viewableRate = 'N'`; `rat_acct = plcy_num` → `'Y'`; otherwise `'Y'` only when the rate account's agent of record is the same agent/agency | Set `viewableRate` |
| PR-BVP-059 | Sales plan is derived from the policy plan description | `'Guaranteed Cost'` → `STAND`; `'Assigned Risk'` → `ARP`; `'State Agency'` → `STATE`; otherwise `RETRO` | Set `salesPlan` |
| PR-BVP-060 | A policy in status `3` is flagged as pending cancel | `WCS_POLICY.PLCY_STAT_CD = '3'` | `pendingCancel = 'Y'` (added 12/22/2025, CYRSWI) |
| PR-BVP-061 | Current certificates flag reflects existence of certificate detail rows | matching `wcs_certificate_detail` rows exist | `currentCerts = 'Y'`; the closed branch hard-codes `'N'` for both `currentCerts` and `viewableRate` |
| PR-BVP-062 | Group name is shown only for unexpired group memberships | `GRP_MBRSHP_EXP_DT > getdate()` | `groupName = isnull(GRP_ASSN_CD, ' ')` |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| List agent online policies | Returns active and recently closed policies for an agent or agency with display flags | `clsPolicySecurity.GetPolicyListForAgent` (`clsPolicySecurity.vb:324-336`) |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>Transact-SQL stored procedure</summary>

```sql
CREATE PROCEDURE dbo.cp_get_agent_online_policy_list
    @AgentNum  int,
    @ForAgency char(3)
AS
-- Active branch (displayType 'O')
SELECT ..., 'O' AS displayType,
       CASE WHEN wp.PLCY_STAT_CD = '3' THEN 'Y' ELSE 'N' END AS pendingCancel,
       -- 12/22/2025 CYRSWI: flag pending cancel
       CASE WHEN pln.descr = 'Guaranteed Cost' THEN 'STAND'
            WHEN pln.descr = 'Assigned Risk'   THEN 'ARP'
            WHEN pln.descr = 'State Agency'    THEN 'STATE'
            ELSE 'RETRO' END                                  AS salesPlan,
       isnull(eg.GRP_ASSN_CD, ' ')                            AS groupName
  FROM WCS_POLICY wp
  ...
  LEFT JOIN group tables ON ... AND ps.GRP_MBRSHP_EXP_DT > getdate()
 WHERE wp.ACTV_PLCY_FL = 'Y'
   AND ((upper(@ForAgency) =  'YES' AND wAgt.agncy_num = @AgentNum)
     OR (upper(@ForAgency) <> 'YES' AND wAgt.agt_num   = @AgentNum))

UNION

-- Closed-within-5-years branch (displayType 'C', currentCerts/viewableRate hard 'N')
SELECT ..., 'C' AS displayType, 'N' AS currentCerts, 'N' AS viewableRate, ...
 WHERE wp.ACTV_PLCY_FL = 'N'
   AND pd.plcy_pd_eff_dt >= DateAdd(YYYY, -5, getDate())
   AND ((upper(@ForAgency) =  'YES' AND wAgt.agncy_num = @AgentNum)
     OR (upper(@ForAgency) <> 'YES' AND wAgt.agt_num   = @AgentNum))

ORDER BY 4   -- policy_key
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server BDS source: `eisD0P11.dbo.cp_get_agent_online_policy_list`
- Legends CLI query executed 2026-08-31: `legends sqlserver query --name cp_get_agent_online_policy_list`
- Calling code: `SAIF.Business.View.Policy/clsPolicySecurity.vb:324-336`